Creating and Managing Databases with Room in Android

Course: Android App Development

Topic: Creating and Managing Databases with Room

Course Link: Android App Development Training

Demo Link: Register for a Course Demo

Website: JustAcademy

1. Introduction to Room Database

Android applications often need to store structured data locally so that information remains available after the app closes or the device temporarily loses internet connectivity. Examples include user profiles, notes, tasks, shopping lists, offline articles, and product records.

Room is an Android Jetpack persistence library that provides an abstraction layer over SQLite. It simplifies database operations through annotations, automatically generated implementations, and compile-time SQL query verification. Room is generally recommended over directly using low-level SQLite APIs for modern Android applications. Read the official Room documentation.

Room does not replace SQLite; it makes working with SQLite more convenient, structured, and maintainable.

2. Learning Objectives

• Understand Room and its role in Android development.
• Learn the differences between Room, SQLite, and DataStore.
• Configure Room dependencies in an Android project.
• Create database entities and define table columns.
• Build Data Access Objects (DAOs) for CRUD operations.
• Create and initialize a Room database.
• Insert, retrieve, update, and delete records.
• Observe database changes with Kotlin Flow.
• Integrate Room with ViewModel and Jetpack Compose.
• Understand relationships, migrations, testing, and database best practices.

3. What Is a Database?

A database is an organized collection of data that can be stored, retrieved, updated, and deleted. In Android, SQLite is a relational database engine available on the device. Room builds a developer-friendly layer on top of SQLite.

Consider a task-management application. Each task might contain an ID, title, description, completion status, and creation timestamp. Instead of keeping all tasks only in memory, the application can store them in a local database.

Each row represents a record, each column represents a property, and the table stores records of the same general structure.

4. Why Use Room?

• Compile-time query validation: Room can detect many SQL query errors while compiling the project.
• Less repetitive code: Annotations reduce the need to write low-level database access code.
• Structured architecture: Entities, DAOs, and database classes have clear responsibilities.
• Coroutines support: Database operations can be written as suspending functions.
• Reactive data: DAO queries can expose Flow so the UI can respond to database changes.
• Migration support: Database schema changes can be managed between app versions.
• Offline support: Locally saved records can remain accessible without an internet connection.

5. Main Components of Room

The typical data flow is:

Jetpack Compose UI
        |
        v
     ViewModel
        |
        v
    Repository
        |
        v
       DAO
        |
        v
   Room Database
        |
        v
      SQLite

6. Room vs SQLite vs DataStore

7. Add Room Dependencies

For a Kotlin Android project using the Room 2.x API, add the runtime and compiler dependencies to the app-level build.gradle.kts file. Use a Room version compatible with your project and configure the Kotlin Symbol Processing (KSP) plugin as required by that version.

plugins {
    // Keep your existing Android and Kotlin plugins.
    id("com.google.devtools.ksp")
}

dependencies {
    val roomVersion = "2.8.5"

    implementation("androidx.room:room-runtime:$roomVersion")
    implementation("androidx.room:room-ktx:$roomVersion")
    ksp("androidx.room:room-compiler:$roomVersion")
}

Check the official documentation for the recommended setup for your project's Room version: Room setup guide. Room 3.x has a different artifact namespace and API setup, so do not mix Room 3.x instructions with Room 2.x dependencies in the same example.

After adding the dependencies, sync the project with Gradle. If KSP is not configured, the compiler-generated database and DAO implementations may not be produced.

8. Creating an Entity

An entity represents a table in the database. Each instance represents a row in that table. The @Entity annotation tells Room to include the class in the database schema.

For this tutorial, create a task-management database.

8.1 Create TaskEntity.kt

package com.example.roomdemo.data

import androidx.room.Entity
import androidx.room.PrimaryKey

@Entity(tableName = "tasks")
data class TaskEntity(
    @PrimaryKey(autoGenerate = true)
    val id: Int = 0,
    val title: String,
    val description: String,
    val isCompleted: Boolean = false,
    val createdAt: Long = System.currentTimeMillis()
)

8.2 Understanding the Entity

Room uses property names as column names by default. Use @ColumnInfo(name = "...") when you need a different database column name. Every entity must have a primary key, either on one field or as a composite key.

9. Creating a Data Access Object (DAO)

A DAO defines the operations that the application can perform on a table. Room generates the implementation of the DAO at compile time.

9.1 Create TaskDao.kt

package com.example.roomdemo.data

import androidx.room.Dao
import androidx.room.Delete
import androidx.room.Insert
import androidx.room.OnConflictStrategy
import androidx.room.Query
import androidx.room.Update
import kotlinx.coroutines.flow.Flow

@Dao
interface TaskDao {

    @Insert(onConflict = OnConflictStrategy.ABORT)
    suspend fun insertTask(task: TaskEntity): Long

    @Insert(onConflict = OnConflictStrategy.ABORT)
    suspend fun insertTasks(tasks: List<TaskEntity>)

    @Update
    suspend fun updateTask(task: TaskEntity)

    @Delete
    suspend fun deleteTask(task: TaskEntity)

    @Query("SELECT * FROM tasks ORDER BY createdAt DESC")
    fun getAllTasks(): Flow<List<TaskEntity>>

    @Query("SELECT * FROM tasks WHERE id = :taskId LIMIT 1")
    suspend fun getTaskById(taskId: Int): TaskEntity?

    @Query("SELECT * FROM tasks WHERE isCompleted = :completed ORDER BY createdAt DESC")
    fun getTasksByStatus(completed: Boolean): Flow<List<TaskEntity>>

    @Query("DELETE FROM tasks WHERE id = :taskId")
    suspend fun deleteTaskById(taskId: Int)

    @Query("UPDATE tasks SET isCompleted = :completed WHERE id = :taskId")
    suspend fun updateTaskStatus(taskId: Int, completed: Boolean)
}

9.2 DAO Annotations

Use suspend for one-time database operations that should be called from a coroutine. A query returning Flow can continuously emit updated results as the observed table changes.

10. Creating the Room Database

The database class connects the entity definitions and DAOs. It extends RoomDatabase and uses the @Database annotation to define the included entities and schema version.

10.1 Create AppDatabase.kt

package com.example.roomdemo.data

import androidx.room.Database
import androidx.room.RoomDatabase

@Database(
    entities = [TaskEntity::class],
    version = 1,
    exportSchema = true
)
abstract class AppDatabase : RoomDatabase() {
    abstract fun taskDao(): TaskDao
}

The database version is initially set to 1. When the schema changes in a later app release, you must plan an appropriate migration or another deliberate schema-update strategy.

11. Initialize Room as a Singleton

Creating many database instances for the same database file is unnecessary and can waste resources. For a typical single-process application, create one shared instance.

11.1 Create DatabaseProvider.kt

package com.example.roomdemo.data

import android.content.Context
import androidx.room.Room

object DatabaseProvider {

    @Volatile
    private var INSTANCE: AppDatabase? = null

    fun getDatabase(context: Context): AppDatabase {
        return INSTANCE ?: synchronized(this) {
            INSTANCE ?: Room.databaseBuilder(
                context.applicationContext,
                AppDatabase::class.java,
                "task_database"
            ).build().also { database ->
                INSTANCE = database
            }
        }
    }
}

Why use applicationContext? It avoids tying the database instance to the lifecycle of an Activity. The synchronized initialization prevents multiple threads from creating competing instances at the same time.

12. Understanding CRUD Operations

CRUD stands for Create, Read, Update, and Delete. These are the basic operations used to manage database records.

13. Inserting Records

Use the DAO's insert function to add a new task. Since the function is suspending, it should run inside a coroutine.

val task = TaskEntity(
    title = "Learn Room",
    description = "Create an Android database",
    isCompleted = false
)

val insertedId = taskDao.insertTask(task)

The generated ID is returned as a Long. With autoGenerate = true, the default ID value of zero is used when creating a new entity and Room assigns the actual database ID during insertion.

14. Reading Records

A simple one-time query can return a list, while a Flow-based query is useful when the UI should update automatically.

14.1 One-Time Query

@Query("SELECT * FROM tasks ORDER BY title ASC")
suspend fun getTasksOnce(): List<TaskEntity>

14.2 Observable Query

@Query("SELECT * FROM tasks ORDER BY title ASC")
fun observeTasks(): Flow<List<TaskEntity>>

With Flow, a UI observing the table can receive updated results after tasks are inserted, updated, or deleted. This avoids manually refreshing the list after every database operation.

15. Updating Records

The @Update annotation updates an existing row based on its primary key. For example, changing a task's completion state can be performed by creating an updated entity with the same ID.

suspend fun markTaskCompleted(task: TaskEntity) {
    taskDao.updateTask(
        task.copy(isCompleted = true)
    )
}

Alternatively, the DAO can use a custom SQL update query when only one or two columns need to change. The query should use bound parameters instead of constructing SQL strings from user input.

16. Deleting Records

You can delete an entity using @Delete or delete by ID with a custom SQL query.

suspend fun removeTask(task: TaskEntity) {
    taskDao.deleteTask(task)
}

suspend fun removeTaskById(taskId: Int) {
    taskDao.deleteTaskById(taskId)
}

Deletion is permanent for the local database unless your application implements a recovery or soft-delete feature.

17. Searching and Filtering Data

Room supports standard SQL conditions. The following example searches task titles using a query parameter.

@Query("SELECT * FROM tasks WHERE title LIKE '%' || :keyword || '%' ORDER BY title")
fun searchTasks(keyword: String): Flow<List<TaskEntity>>

You can also filter records by status, date, category, or another indexed column. Use parameter binding and test queries with representative data to ensure that the results are correct.

18. Handling Duplicate Records and Conflicts

Insert operations can encounter conflicts when a new record uses a primary key or unique value that already exists. Room provides conflict strategies such as ABORT, IGNORE, and REPLACE.

Choose the strategy according to the application's data rules. Do not use REPLACE automatically, because replacing a row can have consequences for related data and database triggers.

19. Using Room with a Repository

A repository separates database implementation details from the UI and ViewModel. It can later combine local database data with a network API or another data source.

19.1 Create TaskRepository.kt

package com.example.roomdemo.data

import kotlinx.coroutines.flow.Flow

class TaskRepository(
    private val taskDao: TaskDao
) {
    val allTasks: Flow<List<TaskEntity>> =
        taskDao.getAllTasks()

    suspend fun addTask(
        title: String,
        description: String
    ) {
        taskDao.insertTask(
            TaskEntity(
                title = title.trim(),
                description = description.trim()
            )
        )
    }

    suspend fun updateTask(task: TaskEntity) {
        taskDao.updateTask(task)
    }

    suspend fun deleteTask(task: TaskEntity) {
        taskDao.deleteTask(task)
    }
}

The repository provides a central place for validation and business rules. For larger applications, you may expose interfaces for repositories to improve testability and simplify dependency injection.

20. Connecting Room to a ViewModel

A ViewModel can expose the repository's Flow to the UI and launch insert, update, and delete operations in its coroutine scope.

package com.example.roomdemo.ui

import androidx.lifecycle.ViewModel
import androidx.lifecycle.viewModelScope
import com.example.roomdemo.data.TaskEntity
import com.example.roomdemo.data.TaskRepository
import kotlinx.coroutines.flow.SharingStarted
import kotlinx.coroutines.flow.stateIn
import kotlinx.coroutines.launch

class TaskViewModel(
    private val repository: TaskRepository
) : ViewModel() {

    val tasks = repository.allTasks.stateIn(
        scope = viewModelScope,
        started = SharingStarted.WhileSubscribed(5000),
        initialValue = emptyList()
    )

    fun addTask(title: String, description: String) {
        if (title.isBlank()) return

        viewModelScope.launch {
            repository.addTask(title, description)
        }
    }

    fun deleteTask(task: TaskEntity) {
        viewModelScope.launch {
            repository.deleteTask(task)
        }
    }

    fun updateTask(task: TaskEntity) {
        viewModelScope.launch {
            repository.updateTask(task)
        }
    }
}

This ViewModel uses constructor injection. Configure a ViewModel factory or a dependency-injection framework such as Hilt to supply the repository. Android will not automatically construct a ViewModel with arbitrary constructor arguments unless the appropriate factory or integration is configured.

21. Displaying Room Data in Jetpack Compose

Compose can observe the ViewModel state and render the saved tasks in a list. The following example shows the essential display logic.

import androidx.compose.foundation.layout.Column
import androidx.compose.foundation.layout.Row
import androidx.compose.foundation.layout.fillMaxWidth
import androidx.compose.foundation.layout.padding
import androidx.compose.foundation.lazy.LazyColumn
import androidx.compose.foundation.lazy.items
import androidx.compose.material3.Button
import androidx.compose.material3.Checkbox
import androidx.compose.material3.MaterialTheme
import androidx.compose.material3.Text
import androidx.compose.runtime.Composable
import androidx.compose.runtime.getValue
import androidx.compose.ui.Modifier
import androidx.compose.ui.unit.dp
import androidx.lifecycle.compose.collectAsStateWithLifecycle
import com.example.roomdemo.data.TaskEntity

@Composable
fun TaskListScreen(viewModel: TaskViewModel) {
    val tasks by viewModel.tasks.collectAsStateWithLifecycle()

    LazyColumn {
        items(
            items = tasks,
            key = { task -> task.id }
        ) { task ->
            TaskRow(
                task = task,
                onDelete = { viewModel.deleteTask(task) },
                onToggle = { completed ->
                    viewModel.updateTask(
                        task.copy(isCompleted = completed)
                    )
                }
            )
        }
    }
}

@Composable
fun TaskRow(
    task: TaskEntity,
    onDelete: () -> Unit,
    onToggle: (Boolean) -> Unit
) {
    Row(
        modifier = Modifier
            .fillMaxWidth()
            .padding(12.dp)
    ) {
        Column(modifier = Modifier.weight(1f)) {
            Text(
                text = task.title,
                style = MaterialTheme.typography.titleMedium
            )
            Text(text = task.description)
        }

        Checkbox(
            checked = task.isCompleted,
            onCheckedChange = onToggle
        )

        Button(onClick = onDelete) {
            Text("Delete")
        }
    }
}

Add the required lifecycle Compose integration dependency if your project does not already include it. The example assumes a properly configured ViewModel instance is provided to the screen.

22. Understanding Database Migrations

When an application is updated, its database schema may need to change. For example, version 1 of a task table might contain a title and description, while version 2 adds a category column. A migration describes how existing data is transformed to the new schema.

Suppose the entity is changed to include a category:

@Entity(tableName = "tasks")
data class TaskEntity(
    @PrimaryKey(autoGenerate = true)
    val id: Int = 0,
    val title: String,
    val description: String,
    val isCompleted: Boolean = false,
    val createdAt: Long = System.currentTimeMillis(),
    val category: String = "General"
)

For an existing database, adding a column usually requires a schema migration. Merely changing the entity class is not enough. A migration must match the actual old and new schemas.

22.1 Example Migration from Version 1 to Version 2

import androidx.room.migration.Migration
import androidx.sqlite.db.SupportSQLiteDatabase

val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(db: SupportSQLiteDatabase) {
        db.execSQL(
            "ALTER TABLE tasks ADD COLUMN category TEXT NOT NULL DEFAULT 'General'"
        )
    }
}

Update the database version to 2 and register the migration when building the database:

@Database(
    entities = [TaskEntity::class],
    version = 2,
    exportSchema = true
)
abstract class AppDatabase : RoomDatabase() {
    abstract fun taskDao(): TaskDao
}

// In the database builder:
Room.databaseBuilder(
    context.applicationContext,
    AppDatabase::class.java,
    "task_database"
)
    .addMigrations(MIGRATION_1_2)
    .build()

Keep schema snapshots under version control and test migrations using representative data. Avoid destructive migrations in production when users' stored data must be preserved. Destructive fallback may delete and recreate the database if a supported migration path is unavailable.

23. Working with Relationships

Relational databases commonly use relationships to connect records. For example, one user may own many tasks. You can represent this relationship with a foreign key.

@Entity(tableName = "users")
data class UserEntity(
    @PrimaryKey(autoGenerate = true)
    val userId: Int = 0,
    val name: String
)

@Entity(
    tableName = "user_tasks",
    foreignKeys = [
        androidx.room.ForeignKey(
            entity = UserEntity::class,
            parentColumns = ["userId"],
            childColumns = ["ownerId"],
            onDelete = androidx.room.ForeignKey.CASCADE
        )
    ],
    indices = [androidx.room.Index("ownerId")]
)
data class UserTaskEntity(
    @PrimaryKey(autoGenerate = true)
    val taskId: Int = 0,
    val ownerId: Int,
    val title: String
)

Here, ownerId references the primary key of the user table. The cascade rule means deleting a user also deletes related task rows. Use cascade behavior only when it matches the application's data-retention rules.

24. Using Transactions

A transaction groups several database operations so they succeed or fail as one logical unit. This is useful when related records must remain consistent.

import androidx.room.Transaction

@Dao
interface TaskManagementDao {
    @Insert
    suspend fun insertTasks(tasks: List<TaskEntity>)

    @Query("DELETE FROM tasks WHERE isCompleted = 1")
    suspend fun deleteCompletedTasks()

    @Transaction
    suspend fun replaceCompletedTasks(tasks: List<TaskEntity>) {
        deleteCompletedTasks()
        insertTasks(tasks)
    }
}

Room supports transactions for appropriate DAO methods. Use them when multiple operations must be atomic, but avoid making transactions longer than necessary.

25. Indexes and Query Performance

An index can improve the performance of queries that repeatedly search or sort using selected columns. Indexes also consume storage and can slow down inserts and updates because the index must be maintained.

@Entity(
    tableName = "tasks",
    indices = [
        androidx.room.Index("isCompleted"),
        androidx.room.Index("createdAt")
    ]
)
data class TaskEntity(
    @PrimaryKey(autoGenerate = true)
    val id: Int = 0,
    val title: String,
    val description: String,
    val isCompleted: Boolean = false,
    val createdAt: Long = System.currentTimeMillis()
)

Do not add indexes to every column. Review common queries and use indexes where they provide a measurable benefit. If you change an existing entity's schema by adding indexes, create and test a migration that adds the indexes to existing databases.

26. Type Converters

Room supports many common types directly, but some custom values need to be converted into database-supported types. A type converter can transform a custom type such as an enum into a String before storing it.

enum class TaskPriority {
    LOW, MEDIUM, HIGH
}

class Converters {
    @androidx.room.TypeConverter
    fun fromPriority(value: TaskPriority): String = value.name

    @androidx.room.TypeConverter
    fun toPriority(value: String): TaskPriority =
        TaskPriority.valueOf(value)
}

Register the converter with the database using @TypeConverters(Converters::class). Ensure that converter behavior remains compatible with existing stored values when the enum or custom type changes.

27. Testing a Room Database

Database tests help verify queries, constraints, and data transformations. A common approach is to use an in-memory Room database for tests so that test data does not persist between runs.

import android.content.Context
import androidx.room.Room
import androidx.test.core.app.ApplicationProvider
import kotlinx.coroutines.runBlocking
import org.junit.After
import org.junit.Assert.assertEquals
import org.junit.Before
import org.junit.Test

class TaskDaoTest {

    private lateinit var database: AppDatabase
    private lateinit var taskDao: TaskDao

    @Before
    fun setup() {
        val context = ApplicationProvider
            .getApplicationContext<Context>()

        database = Room.inMemoryDatabaseBuilder(
            context,
            AppDatabase::class.java
        ).allowMainThreadQueries().build()

        taskDao = database.taskDao()
    }

    @After
    fun closeDatabase() {
        database.close()
    }

    @Test
    fun insertTaskAndReadItBack() = runBlocking {
        taskDao.insertTask(
            TaskEntity(
                title = "Test Room",
                description = "Verify persistence"
            )
        )

        val tasks = taskDao.getTasksByStatus(false)
        val result = kotlinx.coroutines.flow.first(tasks)

        assertEquals(1, result.size)
        assertEquals("Test Room", result.first().title)
    }
}

This example illustrates the test structure; add the appropriate AndroidX test, Room testing, coroutine, and JUnit dependencies to your project. The in-memory database setup shown is for testing only. Avoid allowing main-thread database queries in normal production application code.

28. Common Errors and Troubleshooting

29. Best Practices for Room

• Use entities to model persisted records and define a clear primary key.
• Keep SQL queries inside DAOs rather than scattering SQL across UI components.
• Use suspend functions for one-time reads and writes, and Flow for observable query results.
• Use a shared database instance for a typical single-process app.
• Perform database work away from the main thread.
• Introduce a repository when it improves separation of concerns and testability.
• Use ViewModel state to expose database data to Jetpack Compose.
• Define indexes for frequently searched or sorted fields when justified by query patterns.
• Use transactions when several operations must succeed or fail together.
• Version schemas deliberately and test every migration path.
• Keep database schema snapshots under version control when exporting schemas.
• Validate user input before saving it and apply suitable constraints to persisted data.
• Do not store sensitive information without considering encryption, access controls, and the application's threat model.
• Remember that a local Room database does not automatically synchronize data across devices or with a server.

30. Practical Project: Task Manager App

Project objective: Build a task manager that stores records locally and displays changes immediately.

Required Features

1. Add a task with a title and description.
2. Display all saved tasks in a list.
3. Mark a task as completed or incomplete.
4. Delete an existing task.
5. Search tasks by title.
6. Filter tasks by completion status.
7. Preserve records when the application closes and reopens.
8. Add a category field and implement a database migration.

Suggested Project Structure

com.example.roomdemo
├── data
│   ├── TaskEntity.kt
│   ├── TaskDao.kt
│   ├── AppDatabase.kt
│   ├── DatabaseProvider.kt
│   └── TaskRepository.kt
├── ui
│   ├── TaskViewModel.kt
│   ├── TaskListScreen.kt
│   └── TaskFormScreen.kt
└── MainActivity.kt

Project Evaluation Checklist

• Database and DAO compile successfully.
• CRUD operations work correctly.
• UI updates when stored data changes.
• Data remains available after restarting the app.
• Database operations do not block the main thread.
• Migration preserves existing records.
• Input validation and error states are handled.

31. Interview Questions and Answers

Q1. What is Room in Android?

Room is a Jetpack persistence library that provides an abstraction layer over SQLite for structured local data storage.

Q2. What are the three main components of Room?

The three main components are entities, DAOs, and the RoomDatabase class.

Q3. What is an entity?

An entity is a class annotated with @Entity that defines the structure of a database table.

Q4. What is a DAO?

A Data Access Object defines methods for inserting, retrieving, updating, and deleting data through Room.

Q5. What does @PrimaryKey(autoGenerate = true) mean?

It marks a field as the unique identifier and allows Room to generate an ID when a new row is inserted.

Q6. What is the purpose of @Query?

It defines a custom SQL statement for reading or modifying data in the database.

Q7. What is the difference between suspend and Flow DAO functions?

A suspending DAO function is commonly used for a one-time operation. A Flow-based query can emit new results when the observed database tables change.

Q8. What is a Room migration?

A migration describes how to change a database schema between versions while preserving existing data where required.

Q9. Why should the Room database usually be a singleton?

A shared instance avoids unnecessary database initialization and helps ensure consistent access to the same database in a single-process app.

Q10. What is a type converter?

A type converter transforms a custom type into a database-supported type and back again.

Q11. When should Room be used instead of DataStore?

Use Room for structured records, queries, and relationships. Use DataStore for simple preferences or suitable structured settings that do not require relational database operations.

Q12. Does Room synchronize records with a cloud server?

No. Room stores data locally. Cloud synchronization requires a separate backend or cloud service and synchronization logic.

32. Quick Revision

• Room provides an abstraction over SQLite.
• @Entity defines a table.
• @PrimaryKey identifies each record.
• @Dao defines data-access operations.
• @Insert, @Update, and @Delete support common CRUD actions.
• @Query defines SQL queries.
• @Database declares the entities and database version.
• Flow enables reactive observation of query results.
• Coroutines help run database operations asynchronously.
• Migrations are important when the database schema changes.
• Repositories and ViewModels help separate persistence from the UI.
• Room is local storage and does not automatically provide cloud synchronization.

33. Official Learning Resources

• Android Developers: Save data in a local database using Room
• Android Developers: Define data using Room entities
• Android Developers: Access data using Room DAOs
• Android Developers: App architecture guidance

Conclusion

Room makes it easier to create and manage local databases in Android applications. By defining entities, DAOs, and a database class, developers can implement CRUD operations with less boilerplate and compile-time query validation. When Room is combined with coroutines, Flow, repositories, ViewModels, and Jetpack Compose, it supports responsive and maintainable applications. Correct migrations, sensible indexing, testing, and secure data-handling practices are essential for reliable production apps.

Explore the course: Android App Development Training | Register for a Demo | JustAcademy Homepage